<?php

class Obj_DBTable_Objects extends Zend_Db_Table_Abstract
{
    protected $_name = 'objects';
    protected $_primary = 'ID';
    
    
    public function getObjects($iLocation) {
    	$select = $this->select()->from($this)->order('Name')->where('LocationID = ?', (int) $iLocation);
    	return $this->fetchAll($select)->toArray();
    }
    
    public function getAllPaginatedObjects($page, $active_only = false) {
    	$select = $this->select()->from($this)->order("ID");
		if($active_only) $select = $select->where("Active = 1");
    	$arResult = Zend_Paginator::factory($this->fetchAll($select));
    	$arResult->setItemCountPerPage(BACKEND_ITEMS_PER_PAGE);
    	$arResult->setCurrentPageNumber($page);
    	return $arResult;
    }
    
    public function getAllPaginatedObjectsByCondition($page, $condition, $active_only = false) {
    	$select = $this->select()->from($this)->where($condition)->order("ID");
		if($active_only) $select = $select->where("Active = 1");		
    	$arResult = Zend_Paginator::factory($this->fetchAll($select));
    	$arResult->setItemCountPerPage(BACKEND_ITEMS_PER_PAGE);
    	$arResult->setCurrentPageNumber($page);
    	return $arResult;
    }
    
    public function getAllObjects() {
    	$select = $this->select()->from($this)->order('Name');
    	return $this->fetchAll($select)->toArray();
    }
     
    public function getObject($id, $activeOnly = false) {
    	$select = $this->select()->from($this)->where('ID = ?', (int) $id);
    	if ($activeOnly === true) {
    		$select->where('Active = 1');
    	}
    	$res = $this->fetchRow($select);
    	if ($res) {
    		return $res->toArray();
    	}
    	return false;
    }

    public function findObject($alias, $activeOnly = false) {
    	$select = $this->select()->from($this);
    	if (ctype_digit($alias)) {
    		$select->where('objects.ID = ?', (int) $alias);
    	}
    	else {
    		$select->where('objects.Alias LIKE(?)', trim($alias));
    	}
    	if ($activeOnly === true) {
    		$select->where('objects.Active = 1');
    	}
    	$select->join('countries', 'objects.CountryID = countries.ID', array('CountryName' => 'Name', 'CountryAlias' => 'alias'));
    	$select->join('regions', 'objects.RegionID = regions.ID', array('RegionName' => 'Name', 'RegionAlias' => 'alias'));
    	$select->join('cities', 'objects.CityID = cities.ID', array('CityName' => 'Name', 'CityAlias' => 'alias'));
    	$select->join('locations', 'objects.LocationID = locations.ID', array('LocationName' => 'Name', 'LocationAlias' => 'alias'));
    	$select->setIntegrityCheck(false);
    	$res = $this->fetchRow($select);
    	if ($res) {
    		return $res->toArray();
    	}
    	return false;
    }

    public function getFrontPageObjects() {
    	$select = $this->select()->from($this, array('ID','CountryID','RegionID','CityID','LocationID','Name','Alias','Modified'));
    	$select->where('objects.Active = 1');
    	$select->where('objects.FronPage = 1');
    	$select->join('countries', 'objects.CountryID = countries.ID', array('CountryName' => 'Name', 'CountryAlias' => 'alias'));
    	$select->join('regions', 'objects.RegionID = regions.ID', array('RegionName' => 'Name', 'RegionAlias' => 'alias'));
    	$select->join('cities', 'objects.CityID = cities.ID', array('CityName' => 'Name', 'CityAlias' => 'alias'));
    	$select->join('locations', 'objects.LocationID = locations.ID', array('LocationName' => 'Name', 'LocationAlias' => 'alias'));
    	
    	$select->join('object_files', 'objects.ID = object_files.ParentID AND object_files.Main = 1 AND object_files.Type = '.CONTENT_TYPE_OBJECT.' AND object_files.ObjectType = '.OBJECT_TYPE_OBJECT_PHOTO, array('SourceName', 'ParentID'));
    	$select->order('objects.Sort Desc');
    	$select->setIntegrityCheck(false);
    	return $this->fetchAll($select)->toArray();
    }

	public function getFriendObjects($id) {
		$db = $this->getAdapter();
		$s1 = $db->select()->from('same_objects', array(new Zend_Db_Expr('DISTINCT (IF(SameID='.intval($id).', ObjectID, SameID))')))->where('SameID = ?', (int) $id)->orWhere('ObjectID = ?', (int) $id);
		$select = $this->select()->from($this, array('ID','CountryID','RegionID','CityID','LocationID','Name','Alias'));
		$select->where('objects.Active = 1');
		
		$select->where("objects.ID IN($s1)");
		$select->join('countries', 'objects.CountryID = countries.ID', array('CountryName' => 'Name', 'CountryAlias' => 'alias'));
		$select->join('regions', 'objects.RegionID = regions.ID', array('RegionName' => 'Name', 'RegionAlias' => 'alias'));
		$select->join('cities', 'objects.CityID = cities.ID', array('CityName' => 'Name', 'CityAlias' => 'alias'));
		$select->join('locations', 'objects.LocationID = locations.ID', array('LocationName' => 'Name', 'LocationAlias' => 'alias'));
		
		$select->join('object_files', 'objects.ID = object_files.ParentID AND object_files.Main = 1 AND object_files.Type = '.CONTENT_TYPE_OBJECT.' AND object_files.ObjectType = '.OBJECT_TYPE_OBJECT_PHOTO, array('SourceName', 'ParentID'));
		$select->order('objects.Sort Desc');
		$select->setIntegrityCheck(false);
		
//		echo $select->__toString();
//		die();
		
		return $this->fetchAll($select)->toArray();
	}
    
    
    
    public function searchURLParams($ar) {
    	if (!is_array($ar) || sizeof($ar) <= 0) return false;
    	$select = $this->select()->from($this, array('ID','CountryID','RegionID','CityID','LocationID','Name','Alias','ShortDescription','AreaTo','Area','Сurrency','Price', 'Attribute'));
    	$select->where('objects.Active = 1');
    	$select->join('countries', 'objects.CountryID = countries.ID', array('CountryName' => 'Name', 'CountryAlias' => 'alias'));
    	$select->join('regions', 'objects.RegionID = regions.ID', array('RegionName' => 'Name', 'RegionAlias' => 'alias'));
    	$select->join('cities', 'objects.CityID = cities.ID', array('CityName' => 'Name', 'CityAlias' => 'alias'));
    	$select->join('locations', 'objects.LocationID = locations.ID', array('LocationName' => 'Name', 'LocationAlias' => 'alias'));
    	
    	$select->where('countries.alias = ?', $ar[0]);
    	if (isset($ar[1])) {
    		$select->where('regions.alias = ?', urldecode($ar[1]));
    	}
    	if (isset($ar[2])) {
    		$select->where('cities.alias = ?', urldecode($ar[2]));
    	}
    	if (isset($ar[3])) {
    		$select->where('locations.alias = ?', urldecode($ar[3]));
    	}
    	
    	$select->joinLeft('object_files', 'objects.ID = object_files.ParentID AND object_files.Main = 1 AND object_files.Type = '.CONTENT_TYPE_OBJECT.' AND object_files.ObjectType = '.OBJECT_TYPE_OBJECT_PHOTO, array('SourceName', 'ParentID'));
    	$select->order('objects.Sort DESC');
    	$select->setIntegrityCheck(false);
    	//echo $select->__toString();
    	//die();
    	$arResult = Zend_Paginator::factory($this->fetchAll($select));
    	$arResult->setItemCountPerPage(10);
    	$arResult->setCurrentPageNumber($ar['page']);
    	return $arResult;
    	//return $this->fetchAll($select)->toArray();
    }
    
    public function searchObjects($ar) {
    	if (!is_array($ar) || sizeof($ar) <= 0) return false;
    	$select = $this->select()->from($this, array('ID','CountryID','RegionID','CityID','LocationID','Name','Alias','ShortDescription','AreaTo','Area','Сurrency','Price', 'Attribute'));
    	$select->where('objects.Active = 1');
    	$select->join('countries', 'objects.CountryID = countries.ID', array('CountryName' => 'Name', 'CountryAlias' => 'alias'));
    	$select->join('regions', 'objects.RegionID = regions.ID', array('RegionName' => 'Name', 'RegionAlias' => 'alias'));
    	$select->join('cities', 'objects.CityID = cities.ID', array('CityName' => 'Name', 'CityAlias' => 'alias'));
    	$select->join('locations', 'objects.LocationID = locations.ID', array('LocationName' => 'Name', 'LocationAlias' => 'alias'));
    	
    	$select->where('countries.ID = ?', $ar['country']);
    	$select->where('objects.Attribute = ?', $ar['sale_type']);
    	if ($ar['region'] > 0) $select->where('regions.ID = ?', $ar['region']);
    	if ($ar['city'] > 0) $select->where('cities.ID = ?', $ar['city']);
    	if ($ar['location'] > 0) $select->where('locations.ID = ?', $ar['location']);
    	if ($ar['object_type'] > 0) $select->where('objects.ObjectTypeId = ?', $ar['object_type']);
    	if ($ar['object_price_low'] > 0) $select->where('objects.Price >= ?', $ar['object_price_low']);
    	if ($ar['object_price_high'] > 0) $select->where('objects.PriceTo <= ?', $ar['object_price_high']);
    	if ($ar['object_year'] > 0) $select->where('objects.YearOfConstruction = ?', $ar['object_year']);
    	if ($ar['object_id'] > 0) $select->where('objects.ID = ?', $ar['object_id']);
    	if ($ar['object_area_low'] > 0) $select->where('objects.Area >= ?', $ar['object_area_low']);
    	if ($ar['object_area_high'] > 0) $select->where('objects.AreaTo <= ?', $ar['object_area_high']);
    	if ($ar['object_bedrooms_low'] > 0) $select->where('CAST(objects.NumberOfBedrooms AS SIGNED) >= ?', $ar['object_bedrooms_low']);
    	if ($ar['object_bedrooms_high'] > 0) $select->where('CAST(objects.NumberOfBedrooms AS SIGNED) <= ?', $ar['object_bedrooms_high']);
    	if ($ar['SwimmingPool'] > 0) $select->where('objects.SwimmingPool = 1');
    	if ($ar['Security'] > 0) $select->where('objects.Security = 1');
    	if ($ar['Parking'] > 0) $select->where('objects.Parking = 1');
    	if ($ar['object_see_distance'] > 0) {
			$arObjectSeeDistance = array(
				0 => 1,
				50 => 1,
				100 => 1,
				200 => 1,
				500 => 1,
				1000 => 1,
				2000 => 1,
				5000 => 1,
				10000 => 1,
				999999999 => 1,
			);
    		while(key($arObjectSeeDistance) !== $ar['object_see_distance']) next($arObjectSeeDistance);
    		$prev_val = prev($arObjectSeeDistance);
    		$min = key($arObjectSeeDistance);
    		$select->where('objects.DistanceToSea >= ?', $min);
    		$select->where('objects.DistanceToSea <= ?', $ar['object_see_distance']);
    	}
    	if ($ar['object_air_distance'] > 0) {
			$arObjectAirDistance = array(
				0 => 1,
				10 => 1,
				20 => 1,
				30 => 1,
				40 => 1,
				50 => 1,
				60 => 1,
				80 => 1,
				10000 => 1,
			);
       		while(key($arObjectAirDistance) !== $ar['object_air_distance']) next($arObjectAirDistance);
    		$prev_val = prev($arObjectAirDistance);
    		$min = key($arObjectAirDistance);
    		$select->where('objects.DistanceToAirport >= ?', $min);
    		$select->where('objects.DistanceToAirport <= ?', $ar['object_air_distance']);
    	}
    	
    	$select->joinLeft('object_files', 'objects.ID = object_files.ParentID AND object_files.Main = 1 AND object_files.Type = '.CONTENT_TYPE_OBJECT.' AND object_files.ObjectType = '.OBJECT_TYPE_OBJECT_PHOTO, array('SourceName', 'ParentID'));
    	$select->order('objects.Sort Desc');
    	$select->setIntegrityCheck(false);
    	//echo $select->__toString();
    	//die();
    	$arResult = Zend_Paginator::factory($this->fetchAll($select));
    	$arResult->setItemCountPerPage(10);
    	$arResult->setCurrentPageNumber($ar['page']);
    	return $arResult;
    }
    
    public function getObjectYears() {
    	$select = $this->select()->from($this, array('year' => new Zend_Db_Expr('DISTINCT YearOfConstruction')))->where('Active = 1')->order('YearOfConstruction DESC');
    	return $this->fetchAll($select)->toArray();
    }
    
    public function getSitemapObjects($location) {
    	$select = $this->select()->from($this, array('alias', 'ID'))->where('Active = 1')->where('LocationID = ?', (int) $location)->order('Sort');
    	return $this->fetchAll($select)->toArray();
    }
    
    public function checkAlias($country, $region, $city, $location, $alias) {
    	$select = $this->select()->from($this, array('cnt' => new Zend_Db_Expr('COUNT(ID)')));
    	$select->where('CountryID = ?', (int) $country);
    	$select->where('RegionID = ?', (int) $region);
    	$select->where('CityID = ?', (int) $city);
    	$select->where('LocationID = ?', (int) $location); 
    	$select->where('Alias = ?', trim($alias));
    	$result = $this->fetchRow($select)->toArray();
    	return $result['cnt'] > 0;
    }
    
    public function getLinkRow($id) {
    	$select = $this->select()->from($this, array('a4' => 'alias'));
    	$select->join('locations', 'locations.ID = objects.LocationID', array('a3' => 'alias'));
    	$select->join('cities', 'cities.ID = objects.CityID', array('a2' => 'alias'));
    	$select->join('regions', 'regions.ID = objects.RegionID', array('a1' => 'alias'));
    	$select->join('countries', 'objects.CountryID = countries.ID', array('a0' => 'alias'));
    	$select->where('objects.ID = ?', $id);
    	$select->setIntegrityCheck(false);
    	$res = $this->fetchRow($select);
    	if ($res) {
    		return $res->toArray();
    	}
    	return false;
    }
}